﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace ExcelTools
{
	/// <summary>
	/// Provides a simple wrapper around OleDb to 
	/// access an Excel spreadsheet.
	/// </summary>
	public class ExcelReader
	{
		#region Private Const Fields

		/// <summary>
		/// The template for the OleDb connection string.
		/// </summary>
        private const string mConnectionStringTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\"";

		/// <summary>
		/// The name of the column that contains the sheet name.
		/// </summary>
		private const string mSheetNameColumn = "TABLE_NAME";

		/// <summary>
		/// The name of the schema that contains information about the sheets in the XLS file.
		/// </summary>
		private const string mSheetSchemaContainer = "Tables";

		/// <summary>
		/// Excel SELECT statement for OleDb
		/// </summary>
		private const string mSelectTemplate = "SELECT * FROM [{0}$]";

		#endregion

		#region Private Fields

		/// <summary>
		/// The sheets in the workbook.
		/// </summary>
		private readonly List<ExcelSheet> mSheets = new List<ExcelSheet>();

		#endregion

		#region Private Methods

		/// <summary>
		/// Gets an unopened <see cref="OleDbConnection"/>
		/// for the specified XLS file.
		/// </summary>
		/// <param name="filename"></param>
		/// <returns></returns>
		private static OleDbConnection GetConnection(string filename)
		{
			string connectionString = string.Format(mConnectionStringTemplate, filename);

			return new OleDbConnection(connectionString);
		}

		/// <summary>
		/// Analyzes the schema of the XLS file to determine the names
		/// of all available sheets.
		/// </summary>
		/// <param name="connection">The connection that points to the XLS file.</param>
		/// <returns>All the names of sheets found in the file.</returns>
		private static string[] GetSheetNames(OleDbConnection connection)
		{
			DataTable schema = connection.GetSchema(mSheetSchemaContainer);

			string[] names = new string[schema.Rows.Count];

			for (int i = 0; i < names.Length; i++)
			{
				string name = (string)schema.Rows[i][mSheetNameColumn];

				//Extract the name from any special delimiters, such as $ or '
				if (name.EndsWith("'"))
				{
					name = name.Remove(0, 1);
					name = name.Remove(name.Length - 1);
				}

				if (name.EndsWith("$"))
				{
					name = name.Remove(name.Length - 1);
				}

				names[i] = name;
			}

			return names;
		}

		/// <summary>
		/// Loads all the data from the specified sheet in the 
		/// XLS file pointed to by the connection into a new
		/// <see cref="ExcelSheet"/> and returns it.
		/// </summary>
		/// <param name="connection">The connection to the XLS file.</param>
		/// <param name="sheetName">The name of the sheet to load.</param>
		/// <returns>The sheet with its data.</returns>
		private static ExcelSheet LoadSheet(OleDbConnection connection, string sheetName)
		{
			string query = string.Format(mSelectTemplate, sheetName);

			OleDbCommand command = new OleDbCommand(query, connection);

			ExcelSheet sheet = new ExcelSheet(sheetName);

			using (OleDbDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					//If this is the first row and we don't have column information, populate
					//that collection while loading data.
					bool loadSchema = sheet.Columns.Count == 0;

					ExcelRow row = new ExcelRow();

					for (int i=0; i < reader.FieldCount; i++)
					{
						if (loadSchema)
						{
							sheet.Columns.Add(new ExcelColumn(reader.GetName(i), reader.GetFieldType(i)));
						}

						row.Cells.Add(new ExcelCell(reader.GetValue(i)));
					}

					sheet.Rows.Add(row);
				}
			}

			return sheet;
		}

		#endregion

		#region Public Properties

		/// <summary>
		/// Gets the sheets in the loaded workbook.
		/// </summary>
		public List<ExcelSheet> Sheets
		{
			get
			{
				return mSheets;
			}
		}

		#endregion

		#region Public Constructors

		/// <summary>
		/// Instantiates a reader that will process
		/// the specified file.
		/// </summary>
		/// <param name="filename">The name of the excel file to process.</param>
		public ExcelReader(string filename)
		{
			using (OleDbConnection connection = GetConnection(filename))
			{
				connection.Open();

				string[] sheetNames = GetSheetNames(connection);

				mSheets = new List<ExcelSheet>(sheetNames.Length);

				foreach (string sheetName in sheetNames)
				{
					mSheets.Add(LoadSheet(connection, sheetName));
				}
			}

			return;
		}

		#endregion
	}
}
